package kltn.business;

import java.util.ArrayList;

import kltn.data.Direction;
import kltn.data.InfoTravel;
import kltn.data.Place;

import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

import com.google.android.gms.maps.model.LatLng;

public class BAInfoTravel {

	Context context;
	OpenHelper helper;
	SQLiteDatabase database;

	public BAInfoTravel(Context context) {
		this.context = context;
		helper = new OpenHelper(context);
	}

	public ArrayList<InfoTravel> getInfoByDirect(int direcID) {
		ArrayList<InfoTravel> infoTravels = new ArrayList<>();
		database = helper.getReadableDatabase();
		String sql = "SELECT * FROM Direction_Info,InfoTravel where ID=InfoTravelID and DirectionID="
				+ direcID;
		Cursor c = database.rawQuery(sql, null);
		while (c.moveToNext()) {
			int iD = c.getInt(c.getColumnIndex("ID"));
			int position = c.getInt(c.getColumnIndex("Position"));
			int show = c.getInt(c.getColumnIndex("Show"));
			String name = c.getString(c.getColumnIndex("Name"));
			double laitude = c.getDouble(c.getColumnIndex("Latitude"));
			double longitude = c.getDouble(c.getColumnIndex("Longitude"));
			String audio = c.getString(c.getColumnIndex("Audio"));
			String type = c.getString(c.getColumnIndex("Type"));
			String info = c.getString(c.getColumnIndex("Info"));
			String description = c.getString(c.getColumnIndex("Description"));
			byte[] img = c.getBlob(c.getColumnIndex("Img"));
			Bitmap bmp = null;
			if (img != null)
				bmp = BitmapFactory.decodeByteArray(img, 0, img.length);
			InfoTravel travel = new InfoTravel(iD, name, laitude, longitude,
					audio, type, info, description, bmp);
			travel.setPosition(position);
			travel.setShow(show == 1 ? true : false);
			infoTravels.add(travel);

		}
		database.close();
		return infoTravels;
	}

	public ArrayList<Place> getPlaceFrom() {
		ArrayList<Place> places = new ArrayList<>();
		database = helper.getReadableDatabase();
		String sql = "SELECT DISTINCT Place.ID,Name FROM Direction inner join Place ON Place.ID=Origin";
		Cursor c = database.rawQuery(sql, null);
		while (c.moveToNext()) {
			int iD = c.getInt(c.getColumnIndex("ID"));
			String name = c.getString(c.getColumnIndex("Name"));
			places.add(new Place(iD, name));
		}
		database.close();
		return places;
	}
	
	public ArrayList<Place> getPlaceTo(int Origin) {
		ArrayList<Place> places = new ArrayList<>();
		database = helper.getReadableDatabase();
		String sql = "SELECT DISTINCT Place.ID,Name FROM Direction inner join Place ON Place.ID=Destination WHERE Origin="+Origin;
		Cursor c = database.rawQuery(sql, null);
		while (c.moveToNext()) {
			int iD = c.getInt(c.getColumnIndex("ID"));
			String name = c.getString(c.getColumnIndex("Name"));
			places.add(new Place(iD, name));
		}
		database.close();
		return places;
	}
	public ArrayList<Direction> getDirections(int origin,int destination) {
		ArrayList<Direction> directions = new ArrayList<>();
		DirectionJSON directionJSON = new DirectionJSON();
		database = helper.getReadableDatabase();
		String sql = "SELECT * FROM Direction WHERE Origin="+origin+" AND Destination="+destination;
		Cursor c = database.rawQuery(sql, null);
		while (c.moveToNext()) {
			try {
				int iD = c.getInt(c.getColumnIndex("ID"));
				JSONObject j = new JSONObject(c.getString(c
						.getColumnIndex("JSON")));
				Direction direc = directionJSON.getDirections(j);
				direc.setID(iD);
				directions.add(direc);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		database.close();
		return directions;
	}
	public ArrayList<Direction> getDirections() {
		ArrayList<Direction> directions = new ArrayList<>();
		DirectionJSON directionJSON = new DirectionJSON();
		database = helper.getReadableDatabase();
		Cursor c = database.query("Direction", null, null, null, null, null,
				null);
		while (c.moveToNext()) {
			try {
				int iD = c.getInt(c.getColumnIndex("ID"));
				JSONObject j = new JSONObject(c.getString(c
						.getColumnIndex("JSON")));
				Direction direc = directionJSON.getDirections(j);
				direc.setID(iD);
				directions.add(direc);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		database.close();
		return directions;
	}

	public ArrayList<InfoTravel> getInfoTravelByArray(ArrayList<Integer> IDs,
			int direcID) {
		ArrayList<InfoTravel> infoTravels = new ArrayList<>();
		database = helper.getReadableDatabase();
		String where = "";
		for (Integer id : IDs) {
			where += "ID=" + id + " or ";
		}
		where = where.substring(0, where.length() - 4);
		String sql = "SELECT * FROM Direction_Info,InfoTravel where ID=InfoTravelID and DirectionID="
				+ direcID + " and (" + where + ") Order BY Position";
		Cursor c = database.rawQuery(sql, null);
		while (c.moveToNext()) {
			int iD = c.getInt(c.getColumnIndex("ID"));
			int position = c.getInt(c.getColumnIndex("Position"));
			int show = c.getInt(c.getColumnIndex("Show"));
			String name = c.getString(c.getColumnIndex("Name"));
			double laitude = c.getDouble(c.getColumnIndex("Latitude"));
			double longitude = c.getDouble(c.getColumnIndex("Longitude"));
			String audio = c.getString(c.getColumnIndex("Audio"));
			String type = c.getString(c.getColumnIndex("Type"));
			String info = c.getString(c.getColumnIndex("Info"));
			String description = c.getString(c.getColumnIndex("Description"));
			byte[] img = c.getBlob(c.getColumnIndex("Img"));
			Bitmap bmp = null;
			if (img != null)
				bmp = BitmapFactory.decodeByteArray(img, 0, img.length);
			InfoTravel travel = new InfoTravel(iD, name, laitude, longitude,
					audio, type, info, description, bmp);
			travel.setPosition(position);
			travel.setShow(show == 1 ? true : false);
			infoTravels.add(travel);

		}
		database.close();
		return infoTravels;
	}

	public ArrayList<InfoTravel> getAllInfoTravel() {
		ArrayList<InfoTravel> infoTravels = new ArrayList<>();
		database = helper.getReadableDatabase();
		// String sql="SELECT * FROM InfoTravel";
		// Cursor c=database.rawQuery(sql, null);
		Cursor c = database.query("InfoTravel", null, null, null, null, null,
				null, "200");
		while (c.moveToNext()) {
			int iD = c.getInt(c.getColumnIndex("ID"));
			String name = c.getString(c.getColumnIndex("Name"));
			double laitude = c.getDouble(c.getColumnIndex("Latitude"));
			double longitude = c.getDouble(c.getColumnIndex("Longitude"));
			String audio = c.getString(c.getColumnIndex("Audio"));
			String type = c.getString(c.getColumnIndex("Type"));
			String info = c.getString(c.getColumnIndex("Info"));
			String description = c.getString(c.getColumnIndex("Description"));
			byte[] img = c.getBlob(c.getColumnIndex("Img"));
			Bitmap bmp = null;
			if (img != null)
				bmp = BitmapFactory.decodeByteArray(img, 0, img.length);
			infoTravels.add(new InfoTravel(iD, name, laitude, longitude, audio,
					type, info, description, bmp));
		}
		database.close();
		return infoTravels;
	}

	public ArrayList<InfoTravel> getInfoTravelByLocaltion(InfoTravel infoTravel) {
		ArrayList<InfoTravel> infoTravels = new ArrayList<>();
		database = helper.getReadableDatabase();
		// String sql="SELECT * FROM InfoTravel";
		// Cursor c=database.rawQuery(sql, null);
		Cursor c = database.query("InfoTravel", null, null, null, null, null,
				null);
		while (c.moveToNext()) {
			double latitude = c.getDouble(c.getColumnIndex("Latitude"));
			double longitude = c.getDouble(c.getColumnIndex("Longitude"));
			if (Helper.getDistance(infoTravel.getLatLng(), new LatLng(latitude,
					longitude)) < 50000) {
				int iD = c.getInt(c.getColumnIndex("ID"));
				String name = c.getString(c.getColumnIndex("Name"));
				String audio = c.getString(c.getColumnIndex("Audio"));
				String type = c.getString(c.getColumnIndex("Type"));
				String info = c.getString(c.getColumnIndex("Info"));
				String description = c.getString(c
						.getColumnIndex("Description"));
				byte[] img = c.getBlob(c.getColumnIndex("Img"));
				Bitmap bmp = null;
				if (img != null)
					bmp = BitmapFactory.decodeByteArray(img, 0, img.length);
				infoTravels.add(new InfoTravel(iD, name, latitude, longitude,
						audio, type, info, description, bmp));
			}
		}
		database.close();
		return infoTravels;
	}
}
